1. Field of the Invention
This invention generally relates to the field of database management, and more particularly to a system and method for the efficient control of updates between multiple copies of a database.
2. Description of the Related Art
Databases and database management are widely used in businesses today. Database software organizes collections of data so that its contents can easily be accessed, managed, and updated. One widely used type of database is the relational database. This is a tabular database in which data is defined so that it can be reorganized and accessed in a number of different ways. With networked computers and in particular the Internet there is high usage of distributed databases. The databases are dispersed or replicated among different points in a network, allowing simultaneous access from “local” points of presence, which can span different time zones. The local access allows for fast information exchange without a single point in the network becoming a bottleneck. Additionally, the multiple copies of a database provide data backup for disaster recovery. Although these distributed database solutions are useful, they never the less have several challenges and some shortcomings.
One challenge is that the data in distributed databases changes during usage at the different points of presence in the network. These changes must be communicated to the other database systems to ensure the data timeliness and integrity. Ann example of a distributed database application is a distributed problem management system. Continuing further, suppose an end user has been helped with a notebook problem through a help desk management system at a first geographic location. Later the same end user while traveling requires further assistance regarding the same notebook problem. It is desirable for the help desk management system at a second location to be able access the activity located at the first geographic location. The challenge in this example is that if the distributed databases are not keep synchronized the end user asking for help will be asked to re-explain the history of the problem each time a call is made. Additionally, the help desk operator will not have the benefit of knowing what was tried or suggested in the past. Accordingly, a need exists for a bridging method and system to keep multiple copies of help desk database information accurate as changes are being made to different copies of the data at different database locations.
Another challenge with prior art distributed help desk solutions is the synchronization of problems between two or more companies. For example, with a printer problem, an end user may contact the printer company's help desk. After being directed to try several fixes to the problem it may turn out that the problem may not be with the printer hardware but rather with the printer software. When the end user contacts the software company the user typically must explain the entire problem again. Accordingly, a need exists for a bridging application to enable cooperation between collaborative companies and their databases so as to better server their customers.
Another shortcoming to the distributed databases being at different companies is that the databases may be designed using different database schemas. In fact the databases may not even be from the same provider e.g. IBM DB/2 versus Oracle. Accordingly, a need exists for a bridging application that can work with different database products using different database schemas.
Another shortcoming to the distributed databases being at different companies is that the databases may contain additional relevant information for a given record. This information may be considered sensitive or even confidential. Accordingly, a need exists for a bridging application to be able to exchange parts of databases between different companies without exposing sensitive or confidential entries.
One solution known in the prior art for overcoming the shortcomings is the use of time stamps for managing updates between databases.
TIME STAMP TAG FOR RECORD CONTROL BETWEEN DATABASES 
Turning now to FIG. 1, there is shown a flow diagram 100, illustrating the prior art of controlling distributed databases with the use of a time stamp. The flow is entered at step 102 when there is a need at step 104 to append database record in database #1 306. (Described below) The database record is appended at step 106. A time stamp is attached, at step 108 to the record. If there are no additional changes required at step 110 the flow exits at step 112. If there are additional changes at step 110 the flow is reentered at step 106 when the next record is appended, and the flow continues.
BRIDGE APPLICATION USING A TIME STAMP TAG BETWEEN DATABASES 
Turning now to FIG. 2 there is shown a flow diagram 200, which illustrates the bridging application reconciling remote databases, according to prior art. The flow is entered at step 202 when a bridge application is called to reconcile data at step 204, between all the databases. The bridge application selects a first database to be reconciled at step 206. A query is performed on the first database for any records, to be sent to other databases, at step 208, by comparing the time stamps of the records that have not been sent since the time of the last reconciling between databases. The result of the query is sent to the appropriate remote database(s) at step 210. The remote database(s) receives the result of the query at step 212, and append the received data to its database along with the time stamp. In addition the remote database adjusts the time of synchronization from the first database. The bridge application (not shown) then determines if it has completed the synchronization on all of the databases at step 214. If the synchronization is complete the functional flow exits at step 216. If the bridge application determines that there is additional database to be synchronized at step 214, the functional flow diagram is reentered at step 208. This flow is repeated until the last remote database is reconciled under the control of the bridging application and the flow exits at step 216.
This prior art time stamps method has several shortcomings. The use of time stamps by different organizations in different locations presents a classical coherency problem. Issues such as local time, daylight savings times offsets, the accuracy and diligence of local operators are only some of the problems for a bridging application. In addition, the opportunity exists for an accidental or malicious operator to adjust the local system clock, which will cause miss synchronization. Distributed databases that are not synchronized can consist of records not sent and reconciled; records sent that have already been sent wasting bandwidth, and perhaps duplicating entries at the sent to database. Accordingly, a need exists for a better method to keep track of the records between remote collaborative databases without the use of the time stamps.
One classical method for time synchronization is for all locations to use GMT (Greenwich Median Time). This is used by the broadcast industry. However the setting and synchronization to yet another location brings still additional problems that must be accounted for. That is, each location must have the correct local time and know the correct offset back to Greenwich England. This solution introduces an additional time offset, which is problematic. Accordingly, a need exists for a method to keep track of the records between remote collaborative databases that are time independent.
Another shortcoming is the frequency and amount of time that is used in synchronizing the databases. It is impractical to send the full database frequently and perform a local compare to check that the distributed databases are synchronized. This method consumes too much network bandwidth and storage. Additionally during the times of reconciling many times the database is “off line”. Accordingly, a need exists for a method and system for bridging and transferring only the additions to the database between the different databases, which allows for the timely updates between the distributed databases.
There are prior art solutions that eliminate duplication of records by using timestamps on records and sending only those that have a timestamp greater than that of a certain cached time value. The time value being the timestamp at which the records were last bridged. This has a disadvantage of requiring the bridging application to store a threshold timestamp after each synchronization and hence entering a maintenance state that can slow down the application, as it has to write to disk from time to time. It also has a potential time synchronization problem, as timestamps are usually not absolute but rather relative. If the systems being bridged were physically in different time zones, the meaning of a timestamp can be confusing to the bridging application and hence erroneous in the goal of eliminating duplicate records. This can happen even in spite of using GMT. During the times of clock changes, such as from daylight savings to standard time, confusion can result. Also, someone can inadvertently or maliciously change the time on the bridging application machine at a certain point of time, which will cause the bridging program to use a wrong threshold timestamp to judge which records to send across. Accordingly, a need exists for a solution that does not rely on time stamps to the bridge records between distributed databases.